﻿IF OBJECT_ID('up_KML_Utility') IS not NULL
	DROP PROC up_KML_Utility 

GO

--[up_KML_Utility] 4, 1

CREATE proc [dbo].[up_KML_Utility] 
(
	@UtilityId int,
	@UserId int
)
AS
DECLARE @urlKmlCachePage AS nvarchar(255) 

SELECT 
	@urlKmlCachePage = SettingValue
FROM 
	tb_Settings
WHERE	
	SettingId = 27

SET @UtilityId = 4
SET @UserId = 1
DECLARE @kml xml;

SET @kml = (
	SELECT  
		--General description
		'Pole Position Desktop' AS 'name'
		,'0' AS 'visibility'
		,'1' AS 'open'

		--KML Assets folder
		,(SELECT
			'Assets' AS 'name'
			,'0' AS 'visibility'
			
			,(SELECT
				'Transmission Assets' AS 'name'
				,'0' AS 'visibility'
				,replace(replace(replace(replace(@urlKmlCachePage, '{0}', 'XAssets'), '{1}', @UtilityId), '{2}', @UserId), '{3}', '1') AS 'Link/href'
				FOR XML PATH('NetworkLink'), TYPE
			)
	
			,(SELECT
				'Transmission Lines' AS 'name'
				,'0' AS 'visibility'
				,replace(replace(replace(replace(@urlKmlCachePage, '{0}', 'XLines'), '{1}', @UtilityId), '{2}', @UserId), '{3}', '1') AS 'Link/href'
				FOR XML PATH('NetworkLink'), TYPE
			)

			,(SELECT
				'Distribution Assets' AS 'name'
				,'0' AS 'visibility'
				,replace(replace(replace(replace(@urlKmlCachePage, '{0}', 'DAssets'), '{1}', @UtilityId), '{2}', @UserId), '{3}', '1') AS 'Link/href'
				FOR XML PATH('NetworkLink'), TYPE
			)

			,(SELECT
				'Distribution Lines' AS 'name'
				,'0' AS 'visibility'
				,replace(replace(replace(replace(@urlKmlCachePage, '{0}', 'DLines'), '{1}', @UtilityId), '{2}', @UserId), '{3}', '1') AS 'Link/href'
				FOR XML PATH('NetworkLink'), TYPE
			)
			FOR XML PATH('Folder'), TYPE
		)

		--KML Projects Folder
		,(SELECT 
			'Projects' AS 'name'
			,'0' AS 'visibility'
			,(SELECT 
					projects.[Name] AS 'name'
					,'0' AS 'visibility'
					,replace(replace(replace(replace(@urlKmlCachePage, '{0}', 'Project'), '{1}', projects.Vendor_ProjectId), '{2}', @UserId ), '{3}', '1') AS 'Link/href'
				FROM 
					tb_vendor_projects projects
				WHERE 
					UtilityFk = @UtilityId		
				FOR XML PATH('NetworkLink'), TYPE
			)
			FOR XML PATH('Folder'), TYPE
		)
	
		--KML Maps Folder
		--KML Sections Folder
		
	FOR XML PATH('Document'), TYPE
)

DECLARE @kmlContent AS varchar(max)

SET @kmlContent = REPLACE(CONVERT(varchar(max), (SELECT @kml FOR XML PATH('kml'))), '<kml>', '<?xml version="1.0" encoding="UTF-8"?><kml xmlns="http://earth.google.com/kml/2.2">')

select @kmlContent
